package com.teradata

import java.sql.ResultSet
import java.text.SimpleDateFormat
import java.util.Calendar

import com.alibaba.fastjson.JSONObject
import org.slf4j.LoggerFactory
import utils.{DateUtils, HbaseUtilNew, TeradataConnect}

import scala.collection.mutable

object IopRuleParserMain {

  val log = LoggerFactory.getLogger(IopRuleParserMain.getClass)

  /**
    * 把活动ID相关的 活动配置和客群
    * @param svcType 活动ID
    */
  def iopParse(svcType: String): Unit = {

    log.info("开始解析活动" + svcType + "的配置..")
    log.info("开始连接数据库..")
    val conn = (new TeradataConnect).interface177Connect()
    //    val conn = (new TeradataConnect).portalConnect()
    log.info("数据库已经连接，开始获取此活动相关事件法则..")


    if (svcType.equals("40FC") || svcType.equals("40FA")) {
      // DBODATA.VGOP_MARKETING_TASKS 更新统计日期
      val updateSql = s"UPDATE DBODATA.VGOP_MARKETING_TASKS SET CAL_DATE=DATE WHERE SVC_TYPE='$svcType'"
      val preUpdateSql = conn.prepareStatement(updateSql)
      preUpdateSql.execute()

    }

    // 事件集成立公式
    var evtSetEstablishedFormulas = ""
    // 营销发送的内容
    var message = ""
    // 生效日期
    var efctDate = ""
    // 失效日期
    var endDate = ""
    // 事件源集合（营销活动和事件源类型的关系）
    val evtSourceIdMap: mutable.HashMap[String, String] = new mutable.HashMap
    // 组合事件活动的信息
    val groupActInfoJsonObject = new JSONObject()


    val activeActivitySql =
      "  sel A.EVENT_ID" +
        ",A.SVC_TYPE" +
        ",A.EFCT_DATE" +
        ",A.END_DATE" +
        ",B.EVT_ID" +
        ",C.EVT_SORC_TYPE_ID" +
        ",C.RULE_EST_FORMULA" +
        ",C.ARG_LIST" +
        ",A.USER_SQL " +
        ",A.EVT_EST_FORMULA " +
        ",A.mpTypeId " +
        ",A.OFFER_ID " +
        ",A.SALES_SMS_CONTENT " +
        "from ( " +
        "    SEL C.EVENT_ID (TITLE '事件') as EVENT_ID " +
        ",B.SVC_TYPE (TITLE'营销渠道活动编号') as SVC_TYPE " +
        ",A.USER_SQL (TITLE'取营销目标客户的SQL') as USER_SQL " +
        ",MIN(CAST(EFCT_DATE AS DATE FORMAT 'YYYYMMDD') (VARCHAR(8)))  as EFCT_DATE " +
        ",MAX(CAST(END_DATE AS DATE FORMAT 'YYYYMMDD') (VARCHAR(8))) as END_DATE " +
        ", D.EVT_EST_FORMULA" +
        ", E.mpTypeId " +
        ", F.OFFER_ID " +
        ", A.SALES_SMS_CONTENT " +
        "  FROM PVIEW.VW_VGOP_MARKETING_TASKS A " +
        "  INNER JOIN VGOPDATA.VW_VGOP_MSUBCHANNEL2 B " +
        "    ON A.SVC_TYPE=B.SVC_TYPE " +
        "  LEFT JOIN VGOPDATA.VW_VGOP_MSUBPLANPOLICY2 C " +
        "    ON B.MPSID=C.MPSID " +
        "  LEFT JOIN  NGBASS.IOP_EVT_SET D " +
        "    ON C.EVENT_ID=D.EVT_SET_ID " +
        "  LEFT JOIN VGOPDATA.VW_VGOP_MPLAN2 E " +
        "  ON C.MPID=E.MPID " +
        "  LEFT JOIN VGOPDATA.VW_VGOP_MARKETING_OFFER_REL F" +
        "  ON B.MPSID=F.MPSID" +
        "  WHERE  B.CHANNEL_TYPE>30 " +
        "  AND CHAR(C.EVENT_ID)>0 " +
        "  AND A.CAL_DATE=DATE " +
        s"  AND A.SVC_TYPE = '$svcType'" +
        "  GROUP BY 1,2,3,6,7,8,9 " +
        "  )a " +
        "  LEFT JOIN NGBASS.IOP_EVT_SET_EVT_RELAT b " +
        "    on a.EVENT_ID=b.EVT_SET_ID " +
        "  LEFT JOIN NGBASS.IOP_EVT c " +
        "    on B.EVT_ID=C.EVT_ID "

    log.info(activeActivitySql)
    val preActiveActivitySql = conn.prepareStatement(activeActivitySql)
    val activeActivityResultSet = preActiveActivitySql.executeQuery()

    val hun = new HbaseUtilNew
    val hbaseConn = hun.createHbaseConnection

    try {

      while (activeActivityResultSet.next()) {
        //      本活动对应的事件集id
        //      val evtSetId = activeActivityResultSet.getString(1)
        //      本活动id
        val actId = activeActivityResultSet.getString(2)
        //      本活动客群明细查询sql
        var userPhoneSql = activeActivityResultSet.getString(9)
        //      本活动生效时间
        efctDate = activeActivityResultSet.getString(3)
        //      本活动实效时间
        endDate = activeActivityResultSet.getString(4)
        //      事件id
        val evtId = activeActivityResultSet.getString(5)
        //      事件源id
        var sourceId = activeActivityResultSet.getString(6)
        if (List("4", "6", "7").contains(sourceId)) sourceId = "4"  //6 7都当成位置事件
        //      事件成立公式
//        val evtEstablishedFormulas = activeActivityResultSet.getString(7)
        //      事件法则列表
        val evtSetEstablishedArgString = activeActivityResultSet.getString(8)

//        ?i) 表示所在位置右侧的表达式开启忽略大小写模式
//          (?s) 表示所在位置右侧的表达式开启单行模式。
//          更改句点字符 (.) 的含义，以使它与每个字符（而不是除 \n 之外的所有字符）匹配。
//          注意：(?s)通常在匹配有换行的文本时使用
//          (?m) 表示所在位置右侧的表示式开启指定多行模式。
//          更改 ^ 和 $ 的含义，以使它们分别与任何行的开头和结尾匹配，
//          而不只是与整个字符串的开头和结尾匹配。
//          注意：(?m)只有在正则表达式中涉及到多行的“^”和“$”的匹配时，才使用Multiline模式。
//          上面的匹配模式可以组合使用，比如(?is),(?im)。
//          另外，还可以用(?i:exp)或者(?i)exp(?-i)来指定匹配的有效范围。
        // 将"[","]","空格"，“双引号” 替换成空，忽略大小写，且是多行模式
        // 事件集成立参数列表
        val evtSetEstablishedArgList = evtSetEstablishedArgString.replaceAll("(?im)\\[|\\]|\"|\\s", "").split(",").toList

        //目的是去短信内容 从sql语句中
        message = activeActivityResultSet.getString(13)
//        message = userPhoneSql.replaceAll("(?im)\\s*sel.*?,.*?,\\s*'|'\\s*as\\s+SMS.*", "")
        //      事件集成立公式
        evtSetEstablishedFormulas = activeActivityResultSet.getString(10)
        evtSourceIdMap.update(evtId, sourceId)

        //      MP类型
        val mpTypeId = activeActivityResultSet.getString(11)
        // OFFER_ID
        val offerId = activeActivityResultSet.getString(12)

        val userSqlAppendString = s" AND (SUBS_ID,'$mpTypeId') NOT IN ( " +
          "SEL SUBS_ID,MPTYPE " +
          "FROM PVIEW.VW_IOP_MARKET_SMS_SUBS_D " +
          s"WHERE MPTYPE='$mpTypeId' " +
          "AND CAL_DATE BETWEEN CAST(SUBSTR(DATE,1,6)||'01' AS DATE FORMAT 'YYYYMMDD') AND DATE " +
          ")"

        val freeIdSqlString = " AND MSISDN NOT IN (" +
          s" SEL MSISDN FROM PVIEW.VW_IOP_MARKET_EFCT_OFFER_SUBS WHERE OFFER_ID='$offerId') "

        // 非校园用户
        if (!svcType.equals("40FC") && !svcType.equals("40FA")) {
          userPhoneSql = userPhoneSql + userSqlAppendString + freeIdSqlString
        }
        log.info(userPhoneSql)

        sourceId match {
          case "4" => selectLocationFunc("b_yz_app_td_hbase:SelectPhoneList4")    //位置事件
          case "3" => switchPhoneFunc("b_yz_app_td_hbase:SelectPhoneList3")       //开关机事件
          case "2" => changePhonefunc("b_yz_app_td_hbase:SelectPhoneList2")       //换机事件
          case "5" => appUserFunc("b_yz_app_td_hbase:SelectPhoneList5")           //访问APP事件
          case _ => log.error("Cannot found or recognized eventSourceType!")
        }


        def selectLocationFunc(custTableName: String): Unit = {
          //===========================清空历史配置、客群、基站临时表信息=======================================
          log.info("查询完毕，开始计算历史配置、客群、基站临时表信息..")

          //===========================清空历史配置、客群、基站临时表信息=======================================

          //===========================计算此活动配置信息=======================================

          log.info("开始计算此活动配置信息..")

          //===========================计算此活动配置信息=======================================


          //===========================计算此活动客群=======================================
          log.info("开始计算此活动客群..")
          val lastMonth = getLastMonth
          val custSql = userPhoneSql


          val preCustSql = conn.prepareStatement(custSql)
          val custSqlResult: ResultSet = preCustSql.executeQuery()

          if (actId.equals("40FC") || actId.equals("40FA")) {
            //
            val (deleteSet, putSet) = schoolCustFunc(custSqlResult, custTableName, actId, lastMonth)
            if (deleteSet.size > 0)
              hun.deleteRowsByRowAndQualifier(custTableName, "0", deleteSet.toList)
            if (putSet.size > 0)
              hun.putByKeyColumnList(hbaseConn, custTableName, "0", putSet.toList)
          } else {
            //        客群结果数据
            var rePhoneNoList: List[String] = List()
            while (custSqlResult.next()) {
              val phoneNo = custSqlResult.getString(2)
              val rePhoneNo = phoneNo.substring(phoneNo.length - 2, phoneNo.length) + phoneNo
              rePhoneNoList = rePhoneNo +: rePhoneNoList
            }
            //===========================计算此活动客群=======================================
            hun.putByKeyColumnList(hbaseConn, custTableName, "0", rePhoneNoList.map((_, actId, lastMonth)))
          }

          //===========================计算此活动基站列表=======================================
          log.info("计算完毕，开始计算此活动基站列表..")

          var lacciResultString = ""
          evtSetEstablishedArgList.foreach(ruleId => {

            val formulasSql = s"SEL CAST(PRINC_ARG AS VARCHAR(64000)) FROM ngbass.iop_rule WHERE rule_id ='$ruleId'"
            log.info(formulasSql)
            val preFormulasSql = conn.prepareStatement(formulasSql)
            val formulasResultSet = preFormulasSql.executeQuery()
            var lacciString = ""
            while (formulasResultSet.next()) {
              lacciString = formulasResultSet.getString(1)
            }

            lacciString
              .replaceAll("(?im)\\[|\\]|\"|\\s", "")
              .split(",")
              .foreach(lacci => {
                if (lacciResultString.equals("")) lacciResultString = lacciResultString + lacci
                else lacciResultString = lacciResultString + "," + lacci
              })

          })


          //        key=actid,familyName=0,qualifier=sourceId,value=json
          log.info("开始同步此活动信息到hbase..")

          val actInfoJsonObject = new JSONObject()
          actInfoJsonObject.put("message", message)
          actInfoJsonObject.put("efctDate", efctDate)
          actInfoJsonObject.put("endDate", endDate)
          actInfoJsonObject.put("ResultString", lacciResultString)

          //营销事件配置表
          hun.putByKeyColumn(hbaseConn, "b_yz_app_td_hbase:allOfEffActivity", actId, "0", sourceId, actInfoJsonObject.toJSONString)
          //===========================计算此活动基站列表=======================================
        }

        def schoolCustFunc(custResult: ResultSet, custTableName: String, actId: String, lastMonth: String)
        : (Set[(String, String)], Set[(String, String, String)]) = {
          val currentMonth = DateUtils.getCurrentMonth

          var custResultSet: Set[(String, String)] = Set()
          while (custResult.next()) {
            val phoneNo = custResult.getString(2)
            val rePhoneNo = phoneNo.substring(phoneNo.length - 2, phoneNo.length) + phoneNo
            custResultSet = custResultSet.+((rePhoneNo, lastMonth))
          }

          val TDUserSet = custResultSet.map(_._1)
          log.info("TDUserSet:" + TDUserSet.size)

          val currentActHbaseRes: mutable.HashMap[String, String]
          = hun.getRowsByQualifierFilter(custTableName, "0", actId)

          val unknownActHbaseRes: mutable.HashMap[String, String]
          = hun.getRowsByQualifierFilter(custTableName, "0", "XXXX")


          var deleteSet: Set[(String, String)] = Set()
          var putSet: Set[(String, String, String)] = Set()

          //非初次插入
          if (!currentActHbaseRes.isEmpty) {
            log.info("currenActHbaseRes:" + currentActHbaseRes.size)

            val hbaseUserSet = currentActHbaseRes.map(_._1).toSet
            //      hbase-td
            var hbaseMinusCount = 0
            val hbaseMinusTDSet = currentActHbaseRes.filter(kv => {
              hbaseMinusCount += 1
              if (hbaseMinusCount % 100000 == 0) log.info("hbaseMinusTD progress is : " + hbaseMinusCount * 100.00 / currentActHbaseRes.size + "%")
              !TDUserSet.contains(kv._1)
            })
            log.info("hbaseMinusTD progress is : 100%")
            log.info("hbase-td:" + hbaseMinusTDSet.size)

            //      td-hbase
            var TDMinusCount = 0
            val TDMinusHbaseSet = custResultSet.filter(kv => {
              TDMinusCount += 1
              if (TDMinusCount % 100000 == 0) log.info("TDMinusHbase progress is : " + TDMinusCount * 100.00 / custResultSet.size + "%")
              !hbaseUserSet.contains(kv._1)
            })
            log.info("TDMinusHbase progress is : 100%")
            log.info("td-hbase:" + TDMinusHbaseSet.size)

            hbaseMinusTDSet.foreach(kv => {
              val phoneNo = kv._1
              val saledMonth = kv._2
              if (saledMonth.equals(currentMonth)) {
                deleteSet = deleteSet.+((phoneNo, actId))
                putSet = putSet.+((phoneNo, "XXXX", saledMonth))
              } else {
                deleteSet = deleteSet.+((phoneNo, actId))
              }
            })

            TDMinusHbaseSet.foreach(kv => {
              val TDPhoneNo = kv._1
              val TDMonth = kv._2
              //        如果TD多出人群中包括上一批未知人群的部分，删除列名为XXXX的行、新增列名为当前活动id的行
              if (unknownActHbaseRes.contains(TDPhoneNo)) {
                val saledMonth = unknownActHbaseRes(TDPhoneNo)
                deleteSet = deleteSet.+((TDPhoneNo, "XXXX"))
                putSet = putSet.+((TDPhoneNo, actId, saledMonth))
              }
              //        如果TD多出人群中不包括上一批未知人群,新增人群
              else {
                putSet = putSet.+((TDPhoneNo, actId, TDMonth))
              }
            })

          }
          //初次插入
          else {
            custResultSet.foreach(kv => {
              val TDPhoneNo = kv._1
              val TDMonth = kv._2
              putSet = putSet.+((TDPhoneNo, actId, TDMonth))
            })
          }
          (deleteSet, putSet)
        }

        def switchPhoneFunc(custTableName: String): Unit = {
          log.info("查询完毕，开始计算历史配置、客群、时间段信息..")

          //===========================计算此活动客群=======================================
          log.info("开始计算此活动客群..")
          val lastMonth = getLastMonth
          val custSql = userPhoneSql


          val preCustSql = conn.prepareStatement(custSql)
          val custSqlResult = preCustSql.executeQuery()
          //        客群结果数据
          var rePhoneNoList: List[String] = List()
          while (custSqlResult.next()) {
            val phoneNo = custSqlResult.getString(2)
            val rePhoneNo = phoneNo.substring(phoneNo.length - 2, phoneNo.length) + phoneNo
            rePhoneNoList = rePhoneNo +: rePhoneNoList
          }
          //===========================计算此活动客群=======================================

          //===========================计算此活动业务发生时间=======================================
          log.info("开始计算此活动时间段列表..")
          var timeResultString = ""
          evtSetEstablishedArgList.foreach(ruleId => {
            val formulasSql = s"SEL CAST(PRINC_ARG AS VARCHAR(10000)) FROM ngbass.iop_rule WHERE rule_id ='$ruleId'"
            log.info(formulasSql)
            val preFormulasSql = conn.prepareStatement(formulasSql)
            val formulasResultSet = preFormulasSql.executeQuery()
            var timeRule = ""
            while (formulasResultSet.next()) {
              timeRule = formulasResultSet.getString(1)
            }
            val timeArray = timeRule
              .replaceAll("(?m)\\[|\\]", "")
              .split(",")
              .map(_.replaceAll("(?m)\"", ""))

            if (timeResultString.equals("")) timeResultString = timeResultString + timeArray(0) + "-" + timeArray(1)
            else timeResultString = timeResultString + "," + timeArray(0) + "-" + timeArray(1)
          })

          log.info("开始同步此活动信息到hbase..")
          val actInfoJsonObject = new JSONObject()
          actInfoJsonObject.put("message", message)
          actInfoJsonObject.put("efctDate", efctDate)
          actInfoJsonObject.put("endDate", endDate)
          actInfoJsonObject.put("ResultString", timeResultString)

          hun.putByKeyColumn(hbaseConn, "b_yz_app_td_hbase:allOfEffActivity", actId, "0", sourceId, actInfoJsonObject.toJSONString)
          hun.putByKeyColumnList(hbaseConn, custTableName, "0", rePhoneNoList.map((_, actId, lastMonth)))

          //===========================计算此活动业务发生时间=======================================

        }

        def changePhonefunc(custTableName: String): Unit = {
          switchPhoneFunc(custTableName: String)
        }

        def appUserFunc(custTableName: String): Unit = {
          log.info("查询完毕，开始计算历史配置、客群、时间段信息..")

          //===========================计算此活动客群=======================================
          log.info("开始计算此活动客群..")
          val lastMonth = getLastMonth
          val custSql = userPhoneSql


          val preCustSql = conn.prepareStatement(custSql)
          val custSqlResult = preCustSql.executeQuery()
          //        客群结果数据
          var rePhoneNoList: List[String] = List()
          while (custSqlResult.next()) {
            val phoneNo = custSqlResult.getString(2)
            val rePhoneNo = phoneNo.substring(phoneNo.length - 2, phoneNo.length) + phoneNo
            rePhoneNoList = rePhoneNo +: rePhoneNoList
          }
          //===========================计算此活动业务发生时间=======================================
          log.info("开始计算APP类活动APP列表..")
          var keyWordResultString = ""    // 用户访问的关键字
          var startDate = ""
          var endDate = ""
          var startHour = ""
          var endHour = ""
          // 事件集成立参数列表
          evtSetEstablishedArgList.foreach(ruleId => {
            val formulasSql = s"SEL CAST(PRINC_ARG AS VARCHAR(10000)) FROM ngbass.iop_rule WHERE rule_id ='$ruleId'"
            log.info(formulasSql)
            val preFormulasSql = conn.prepareStatement(formulasSql)
            val formulasResultSet = preFormulasSql.executeQuery()


            while (formulasResultSet.next()) {
              val appRuleArray = formulasResultSet
                .getString(1)
                .replaceAll("(?m)\\[|\\]", "")
                .replaceAll("\"", "")
                .split(",")


              appRuleArray(0).split(" ").foreach(
                keyword => {
                  if (keyWordResultString.equals("")) keyWordResultString = keyword
                  else keyWordResultString = keyWordResultString + "-" + keyword
                }
              )
              startDate = appRuleArray(1).replaceAll("-", "")
              endDate = appRuleArray(2).replaceAll("-", "")
              startHour = appRuleArray(3)
              endHour = appRuleArray(4)
            }

          })
          log.info("开始同步此活动信息到hbase..")
          val actInfoJsonObject = new JSONObject()
          actInfoJsonObject.put("message", message)
          actInfoJsonObject.put("efctDate", efctDate)
          actInfoJsonObject.put("endDate", endDate)
          actInfoJsonObject.put("ResultString", keyWordResultString + "," + startDate + "," + endDate + "," + startHour + "," + endHour)

          hun.putByKeyColumn(hbaseConn, "b_yz_app_td_hbase:allOfEffActivity", actId, "0", sourceId, actInfoJsonObject.toJSONString)
          hun.putByKeyColumnList(hbaseConn, custTableName, "0", rePhoneNoList.map((_, actId, lastMonth)))
        }
      }

      //    组合事件
      if (evtSetEstablishedFormulas.contains("&&") || evtSetEstablishedFormulas.contains("||")) {
        evtSourceIdMap.foreach(evtSourceId => {
          val evtId = evtSourceId._1
          val sourceId = evtSourceId._2
          evtSetEstablishedFormulas = evtSetEstablishedFormulas.replaceAll(evtId, sourceId)
        })
        groupActInfoJsonObject.put("message", message)
        groupActInfoJsonObject.put("efctDate", efctDate)
        groupActInfoJsonObject.put("endDate", endDate)
        groupActInfoJsonObject.put("ResultString", evtSetEstablishedFormulas)

        //      说明有事件组合情况
        hun.putByKeyColumn(hbaseConn, "b_yz_app_td_hbase:allOfEffActivityGroup", svcType, "0", "0", groupActInfoJsonObject.toJSONString)
      }

    }
    finally {
      if (conn != null) conn.close()
      if (hbaseConn != null) hbaseConn.close()
    }

    log.info("计算完毕，活动信息已经同步到hbase。")
  }

  def getLastMonth: String = {
    val dateFormat: SimpleDateFormat = new SimpleDateFormat("yyyyMM")

    val cal: Calendar = Calendar.getInstance()

    cal.setTime(cal.getTime);
    cal.add(Calendar.MONTH, -1)
    val lastMonth = dateFormat.format(cal.getTime())
    lastMonth
  }


  def main(args: Array[String]): Unit = {
//    iopParse("5D35")

    iopParse("6EC3")
  }

}
